#include "excel.h"

#include <QDebug>
#include <QFileDialog>
#include <QMessageBox>

Excel::Excel(QObject* parent)
    : QObject(parent)
    , excelApp(new QAxObject("Excel.Application"))
{
//        excelApp->setProperty("Visible", true); // 设置是否显示Excel
    //设置是否显示警告信息。如果为true那么在关闭是会出现类似“文件已修改，是否保存”的提示; 一定要设为false
    excelApp->setProperty("DisplayAlerts", false);
    QAxObject* workBooks = this->excelApp->querySubObject("WorkBooks");
    workBooks->dynamicCall("Add");
    this->workBook = this->excelApp->querySubObject("ActiveWorkBook");
    this->workSheets = this->workBook->querySubObject("WorkSheets");
    this->curWorkSheet = this->workSheets->querySubObject("Item(int)", 1);

    connect(excelApp, SIGNAL(exception(int, QString, QString, QString)),
        this, SLOT(dealException(int, QString, QString, QString)));
    connect(workBook, SIGNAL(exception(int, QString, QString, QString)),
        this, SLOT(dealException(int, QString, QString, QString)));
    connect(workSheets, SIGNAL(exception(int, QString, QString, QString)),
        this, SLOT(dealException(int, QString, QString, QString)));
    connect(curWorkSheet, SIGNAL(exception(int, QString, QString, QString)),
        this, SLOT(dealException(int, QString, QString, QString)));
}

Excel::~Excel()
{
    workBook->dynamicCall("Close()"); // 关闭工作簿
    excelApp->dynamicCall("Quit()"); // 退出Excel程序
    delete excelApp;
    excelApp = nullptr;
}

void Excel::addSheet(const QString& sheetName)
{
    this->workSheets->dynamicCall("Add"); // 默认在最前面添加工作表
    //    this->workSheet = this->workBook->querySubObject("WorkSheets(int)", 1); // 与下句等效
    this->curWorkSheet = this->workSheets->querySubObject("Item(int)", 1); // 获取新增的位于第一的工作表
    this->curWorkSheet->setProperty("Name", sheetName);
}
// property: Row  Column Count Value Name

void Excel::appendRow(const QVariantList& oneRow)
{
    int c = oneRow.count();
    this->curRow++;
    this->rowsData.append(QVariant(oneRow));
    char colToken = 'A' + c - 1;
    QString szRange = QString("A%2:%1%2").arg(colToken).arg(curRow);

    this->range = this->curWorkSheet->querySubObject("Range(const QString&)", szRange);
    //    QAxObject *cell = worksheet->querySubObject("Cells(int, int)", i, j);

    //    this->range->setProperty("Value", oneRow);  // 使用WPS时，这个不能写入数据，下句可以
    this->range->dynamicCall("SetValue(const QVariant&)", oneRow);
    this->curWorkSheet->querySubObject("Range(const QString&)", "1:1")->setProperty("RowHeight", 30); //调整第一行行高
}

void Excel::open(QString file)
{
    if (file.isEmpty()) {
        file = QFileDialog::getOpenFileName(nullptr, tr("选择待打开的Excel文件："), "./untitled.xlsx",
            "Excel Files (*.xlsx *.xls)", nullptr,
            QFileDialog::DontUseNativeDialog);
        if (file.isEmpty()) {
            return;
        }
    }
    QAxObject* workBooks = excelApp->querySubObject("WorkBooks");
    workBooks->dynamicCall("Open (const QString&)", file);
    this->workBook = excelApp->querySubObject("ActiveWorkBook");
    this->workSheets = this->workBook->querySubObject("WorkSheets");
    this->curWorkSheet = this->workBook->querySubObject("Worksheets(int)", 1);

    QString name = this->curWorkSheet->property("Name").toString();
    int count = this->workSheets->property("Count").toInt();
    QString info = QString("Total %1 sheets; Now at sheet 1 with Name %2").arg(count).arg(name);
    qInfo() << info;

    range = curWorkSheet->querySubObject("UsedRange");
}

void Excel::save(QString file)
{
    if (file.isEmpty()) {
        file = QFileDialog::getSaveFileName(nullptr, tr("保存Excel文件："), "./untitled.xlsx",
            "Excel Files (*.xlsx *.xls)", nullptr,
            QFileDialog::DontUseNativeDialog);
        if (file.isEmpty()) {
            return;
        }
    }

    for (int var = 1; var <= getSheetsCount(); ++var) {
        QAxObject* ws = this->workBook->querySubObject("Worksheets(int)", var);
        QAxObject* range = ws->querySubObject("UsedRange");
        range->querySubObject("Font")->setProperty("Size", 14); // 设置字号
        range->setProperty("VerticalAlignment", -4108); // 设置垂直居中
        range->setProperty("HorizontalAlignment", -4108); // 设置水平居中
        range->querySubObject("Columns")->dynamicCall("AutoFit");

        range = ws->querySubObject("Range(const QString&)", "1:1"); // 标题
        range->querySubObject("Font")->setProperty("Size", 16); // 设置字号
        range->querySubObject("Font")->setProperty("Bold", true);
        range->querySubObject("Font")->setProperty("Color", QColor("green")); // 设置颜色
        range->querySubObject("Font")->setProperty("Name", "微软雅黑"); // 设置字体
        range->setProperty("VerticalAlignment", -4108); // 设置垂直居中
        range->setProperty("HorizontalAlignment", -4108); // 设置水平居中
    }

    //保存至file，注意一定要用QDir::toNativeSeparators将路径中的"/"转换为"\"，否则产生异常
    QVariant v = this->workBook->dynamicCall("SaveAs(const QString&)", QDir::toNativeSeparators(file));
    if (v.isValid()) {
        QMessageBox::information(nullptr, tr("保存Excel："), QString("<h2>成功导出到Excel文件:</h2>\n").append(file));
    }

    return;
}

int Excel::getSheetsCount()
{
    return workSheets->property("Count").toInt();
}

int Excel::getRowsCount()
{
    return range->querySubObject("Rows")->property("Count").toInt();
}

int Excel::getColumnsCount()
{
    return range->querySubObject("Columns")->property("Count").toInt();
}

QVariant Excel::getCell(int r, int c)
{
//    return range->querySubObject("Cells(int,int)", r, c)->property("Value");
    return range->querySubObject("Cells(int,int)", r, c)->dynamicCall("Value");
}

void Excel::setCell(int r, int c, QVariant v)
{
    curWorkSheet->querySubObject("Cells(int,int)", r, c)->setProperty("Value", v);
}

void Excel::setCurSheet(const QString& name)
{
    curWorkSheet = workSheets->querySubObject("Item(const QString&)", name);
    this->curRow = 0;
}

void Excel::setCurSheet(int index)
{
    curWorkSheet = workSheets->querySubObject("Item(int)", index);
    this->curRow = 0;
}

void Excel::delSheet(const QString& name)
{
    workSheets->querySubObject("Item(QString)", name)->dynamicCall("delete");
}

void Excel::delSheet(int index)
{
    workSheets->querySubObject("Item(int)", index)->dynamicCall("delete");
}

void Excel::dealException(int code, QString src, QString desc, QString help)
{
    Q_UNUSED(code);
    Q_UNUSED(src);
    Q_UNUSED(help);
    QMessageBox::critical(nullptr, tr("保存Excel："), QString("<h2>失败！遇到的问题：</h2>\n").append(desc));
}
